# coding=utf-8

import requests
from bs4 import BeautifulSoup

import sqlite3
from sqlite3 import OperationalError
import os

from rich import logging, print
from rich.table import Table 
from rich.text import Text

import click

import init


DB_FILENAME = "nbdp.sqlite3"
CHUXUEZHE_URL = "http://begin.nbdp.net/oj/userinfo.php?user={}"


if not os.path.exists(DB_FILENAME):
    open(DB_FILENAME, 'w', encoding="utf-8")

DB_CONNECTION = sqlite3.connect(DB_FILENAME)


POWER_FACTORS = {
    "CHUXUEZHE_SOLVED": 10,
    "CHUXUEZHE_SOLVED_PERCENTAGE": 1000
}


SQL_FIELDS_EXT = {
    '基础战力值': f"chuxuezhe_solved*{POWER_FACTORS['CHUXUEZHE_SOLVED']}+{POWER_FACTORS['CHUXUEZHE_SOLVED_PERCENTAGE']}*chuxuezhe_solved/chuxuezhe_submits as 基础战力值",
    '通过率': "round(1.0*chuxuezhe_solved/chuxuezhe_submits, 3) as 通过率",
}


SQL_DEFAULT_FIELDS_RANK = [
    "username as 姓名", 'chuxuezhe_id as ID','chuxuezhe_solved as 已解决', 'chuxuezhe_submits-chuxuezhe_solved as 错误', 'chuxuezhe_submits as 总提交', 
    SQL_FIELDS_EXT['通过率'], SQL_FIELDS_EXT['基础战力值'], 'teacher as BOSS', 'datetime(updated_at, "localtime") as 更新时间'
]


def init_db():
    cur = DB_CONNECTION.cursor()
    cur.execute('''
        CREATE TABLE IF NOT EXISTS users(
            'username' VARCHAR(32) NOT NULL PRIMARY KEY,
            'chuxuezhe_id' VARCHAR(32) NOT NULL, 
            'chuxuezhe_solved' INT DEFAULT 0,
            'chuxuezhe_submits' INT DEFAULT 0,
            'updated_at' TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    DB_CONNECTION.commit()



# 根据初学者ID获取账户数据
def get_chuxuezhe_data(chuxuezhe_id):
    resp = requests.get(CHUXUEZHE_URL.format(chuxuezhe_id))
    soup = BeautifulSoup(resp.content, 'html.parser')
    rows = soup.find_all("tr")[0:-3]

    if len(rows) < 3:
        return None

    result = {}

    rank_tr = rows[0]
    rank_tr.td.td.td.decompose()
    rank = rank_tr.td.td.string
    result["名次"] = int(rank)

    solved_tr = rows[1]
    solved_count = solved_tr.td.td.a.string
    result["解决"] = int(solved_count)

    for tr in rows[2:]:
        if tr.td.td.td:
            tr.td.td.td.decompose()
        value = tr.td.td.string
        tr.td.td.decompose()
        key = tr.td.string
        result[key] = int(value)
    
    result["提交"] -= result["正确"] - result["解决"]

    return result


# 添加初学者账号
def add_chuxuezhe(username, chuxuezhe_id, update=False):
    cur = DB_CONNECTION.cursor()
    sql = f'''
        INSERT INTO users(username, chuxuezhe_id) 
        VALUES ('{username}', '{chuxuezhe_id}')
    '''
    try:
        cur.execute(sql)
        DB_CONNECTION.commit()
    except:
        return

    if update:
        userdata = get_chuxuezhe_data(chuxuezhe_id)
        if userdata is None:
            print("获取初学者用户数据失败：", username, chuxuezhe_id);
            return

        sql = f'''
            UPDATE users
            SET chuxuezhe_submits='{userdata['提交']}', chuxuezhe_solved='{userdata['解决']}'
            WHERE chuxuezhe_id='{chuxuezhe_id}'
        '''
        try:
            cur.execute(sql)
            DB_CONNECTION.commit()
            print(Text('已更新初学者：'+username+', '+chuxuezhe_id))
        except Exception as e:
            print(Text("数据库更新初学者数据失败:"+str(e)))


def update_user(u):
    sql = f'''
        INSERT INTO users(username, chuxuezhe_id, teacher)
        VALUES ('{u['username']}', '{u['chuxuezhe_id']}', '{u['teacher']}')
        ON CONFLICT(username)
        DO UPDATE SET teacher='{u['teacher']}', chuxuezhe_id='{u['chuxuezhe_id']}'
    '''

    cur = DB_CONNECTION.cursor()
    try:
        cur.execute(sql)
        DB_CONNECTION.commit()
    except Exception as e:
        print(e)


# 更新ID对应的初学者数据到数据库
def update_chuxuezhe_data(chuxuezhe_id):
    data = get_chuxuezhe_data(chuxuezhe_id)
    if not data:
        return
    cur = DB_CONNECTION.cursor()
    cur.execute(f'''
        UPDATE users 
        SET chuxuezhe_submits='{data['提交']}',chuxuezhe_solved='{data['解决']}', updated_at=CURRENT_TIMESTAMP
        WHERE chuxuezhe_id='{chuxuezhe_id}'
    ''')
    DB_CONNECTION.commit()

# 更新所有初学者数据到数据库
def update_all_chuxuezhe_data(intervals=60):
    cur = DB_CONNECTION.cursor()
    sql = 'SELECT chuxuezhe_id,julianday(CURRENT_TIMESTAMP)*86400-julianday(updated_at)*86400 FROM users' 
    users = cur.execute(sql).fetchall()
    for u in users:
        if (u[1] < intervals):
            continue
        data = get_chuxuezhe_data(u[0])
        cur.execute(f'''
            UPDATE users 
            SET chuxuezhe_submits='{data['提交']}',chuxuezhe_solved='{data['解决']}', updated_at=CURRENT_TIMESTAMP
            WHERE chuxuezhe_id='{u[0]}'
        ''')
    DB_CONNECTION.commit()

# 显示初学者ID对应的数据，如果update为True，则先更新再显示，否则只显示本地数据
def show_chuxuezhe_data(chuxuezhe_id, update=False):
    if update:
        update_chuxuezhe_data(chuxuezhe_id)

    cur = DB_CONNECTION.cursor()
    data = cur.execute(f'''
        SELECT {','.join(SQL_DEFAULT_FIELDS_RANK)}
        FROM users
        WHERE chuxuezhe_id='{chuxuezhe_id}'
    ''').fetchone()

    if not data:
        return

    columns = [f if 'as' not in f else f.split('as')[1].strip() for f in SQL_DEFAULT_FIELDS_RANK]

    table = Table()
    for k in columns:
        table.add_column(k, justify="center", style="cyan", no_wrap=True)
    
    table.add_row(*map(str, data), style="green")
    print(table)



def show_chuxuezhe_rank(title=None, fields=SQL_DEFAULT_FIELDS_RANK, where=None, orderby="基础战力值 desc"):
    if fields is None or len(fields)==0:
        print(Text("没有指定字段"))
        return

    fields_str = ','.join(fields)
    sql = f"SELECT {fields_str} FROM users"

    if where:
        sql = sql + f" WHERE {where}"

    if orderby:
        sql = sql + f' order by {orderby}'

    cur = DB_CONNECTION.cursor()
    try:
        users = cur.execute(sql).fetchall()
    except OperationalError as e:
        print(e)
        return

    if len(users) == 0:
        print(Text("没有数据"))
        return

    columns = [f if 'as' not in f else f.split('as')[1].strip() for f in fields]        

    table  = Table(title=title, show_lines=True)

    table.add_column("排名", justify='right', no_wrap=True)
    for c in columns:
        table.add_column(c.upper(), justify='right', no_wrap=True)            
    
    for i, u in enumerate(users):
        if u[2]>=400 or i+1<=5:
            table.add_row(str(i+1), *map(str, u), style="white on red")
        elif u[2]>=300:
            table.add_row(str(i+1), *map(str, u), style="red")
        elif u[2]>=200:
            table.add_row(str(i+1), *map(str, u), style="yellow")
        elif u[2]>=100:
            table.add_row(str(i+1), *map(str, u), style="green")
        else:
            table.add_row(str(i+1), *map(str, u), style="white")

    print(table)

COMMANDS = {
    'rank': show_chuxuezhe_rank
}

@click.command()
@click.option('--comamnd', prompt='请输入指令', help='The person to greet.')
def cli(comamnd):
    if comamnd in COMMANDS:
        COMMANDS[comamnd]()
    else:
        print("命令无效")

if __name__ == '__main__':
    cli()
    init_db()
    update_all_chuxuezhe_data(intervals=600)
    show_chuxuezhe_rank()
